﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Shutterstock.Salesforce.Tools;
using Shutterstock.Salesforce.SSISDownload.Logging;
using Shutterstock.Salesforce.Tools.SFPartner;
using LumenWorks.Framework.IO.Csv;
using System.IO;
namespace Shutterstock.Salesforce.SSISDownload.SFTableOperations
{
 /// <summary>
    /// Represnets a table updater -- update any changes made
    /// get all modifed rows, remove it from sf sql database and insert modified data
    /// </summary>
    public static class TableUpdater
    {
        /// <summary>
        /// Update the given table
        /// </summary>
        /// <param name="rp"></param>
        /// <param name="keepStagingTable">Whether to Keep or drop the staging Table after user</param>
        /// <returns>The name of the staging table if it exists, String.Empty otherwise</returns>
        public static string Update(ReplicationParameter rp, bool keepStagingTable)
        {
            if(!rp.SFTableInfo.HasModifiedDateField())
                return String.Empty;

            using (SqlConnection Dest = new SqlConnection(rp.DestConnectionString))
            {
                
                Dest.Open();
                
                // check to see if the table exists in the sql server database
                SqlCommand cmd = Dest.CreateCommand();
                cmd.CommandText = string.Format("select object_id from sys.objects where name = '{0}'", rp.SFTableInfo.TableName);
                object obj = cmd.ExecuteScalar();
                if (obj is DBNull)
                    return String.Empty;


                // get the last system mod date in the sql server database table
                string modifiedField = rp.SFTableInfo.GetModifiedDateField();
                string modifiedFieldParaName = String.Format("@{0}", modifiedField);
                cmd = Dest.CreateCommand();
                cmd.CommandText = string.Format("select max({0}) from [{1}]", modifiedField, rp.SFTableInfo.TableName);
                obj = cmd.ExecuteScalar();
                DateTime lastModified = new DateTime(1970, 1, 1);
                // if no change or last modified date is 30 days before just do nothing
                if (obj is DBNull || (DateTime)obj < DateTime.Today.AddDays(-29))
                    return String.Empty;
                else
                    lastModified = ((DateTime)obj).AddMinutes(-15);

                string query = string.Format(String.Format("{0}where {1} >= {{0}}", rp.SFTableInfo.GenerateSelectAll(), modifiedField), lastModified.ToString("yyyy-MM-ddTHH:mm:sszzz"));
                
                string CountQuery = String.Format("Select COUNT() from {0} where {1} >= {2}", rp.SFTableInfo.TableName, modifiedField, lastModified.ToString("yyyy-MM-ddTHH:mm:sszzz"));
                
                cmd.CommandText = rp.SFTableInfo.GenerateCreateUpdateStagingTable();
                cmd.ExecuteNonQuery();

                DateTime start = DateTime.Now;
                int rows=0;
                if (!rp.UseBulkAPI)
                {
                    rows = UseStandardAPI(rp, Dest, cmd, query, start);
                }
                else
                {
                    QueryResult qr = rp.SalesForceConnection.SalesForceService.query(CountQuery);
                    int affectedRecords = 0;
                    if (qr.size > 0)
                    {
                        affectedRecords = qr.size;
                    }
                    if (affectedRecords > rp.BulkRowCutoff)
                    {

                        rows = UseBulkAPI(rp, Dest, cmd, query, start);
                    }
                    else
                    {
                        rows = UseStandardAPI(rp, Dest, cmd, query, start);
                    }
                    


                }
                //Construct Update Statement

                string stagingName = rp.SFTableInfo.GetStagingUpdateTableName();
                bool needsComma = false;
                StringBuilder statement = new StringBuilder();
                statement.AppendLine(String.Format("Update [{0}]", rp.SFTableInfo.TableName));
                statement.AppendLine(" SET ");
                foreach (string columnName in rp.SFTableInfo.ColumnNames)
                {
                    if (needsComma)
                        statement.Append(",");
                    statement.AppendLine(String.Format("{0} = {1}.{0}", columnName, stagingName));
                    needsComma = true;
                }
                statement.AppendLine(" FROM " + stagingName);
                statement.AppendLine(String.Format(" WHERE [{0}].id = {1}.id ", rp.SFTableInfo.TableName, stagingName));


                StringBuilder drop = new StringBuilder();
                drop.AppendLine(String.Format(" IF object_id('{0}') is not null", stagingName));
                drop.AppendLine(" begin");
                drop.AppendLine(String.Format(" Drop Table [{0}] ", stagingName));
                drop.AppendLine(" end");


                start = DateTime.Now;              
                SqlCommand updateCommand = Dest.CreateCommand();
                updateCommand.CommandTimeout = 0;
                updateCommand.CommandText = rp.SFTableInfo.CreateIDIndexForUpdateTable();
                updateCommand.ExecuteNonQuery();
                updateCommand.CommandText = statement.ToString();
                updateCommand.ExecuteNonQuery();
                if (!keepStagingTable)
                {
                    updateCommand.CommandText = drop.ToString();
                    updateCommand.ExecuteNonQuery();
                }
                Logger.Log(FunctionType.UpdateCmd, rp.SFTableInfo.TableName, TableTaskType.Update, start, rows);
                
                return  (keepStagingTable ? stagingName:String.Empty);
            }
            

        }
        private static int UseStandardAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start)
        {
            int rows;

            DataTable dt = rp.SalesForceConnection.SalesForceService.GetDataTable(query, rp.SFTableInfo);
            Logger.Log(FunctionType.WebServiceGetTable, rp.SFTableInfo.TableName, TableTaskType.Update, start, dt.Rows.Count);


            start = DateTime.Now;
            // using Builk Insert.
            cmd.CommandText = rp.SFTableInfo.GenerateCreateUpdateStagingTable();
            cmd.ExecuteNonQuery();

            using (SqlBulkCopy BulkDestination = new SqlBulkCopy(Dest))
            {
                BulkDestination.DestinationTableName = rp.SFTableInfo.GetStagingUpdateTableName();
                BulkDestination.BulkCopyTimeout = 0;
                BulkDestination.BatchSize = rp.BatchSize;
                BulkDestination.WriteToServer(dt);
            }
            rows = dt.Rows.Count;
            return rows;
        }

        private static int UseBulkAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start)
        {
            var bqs = rp.SalesForceConnection;
            string path = String.Format("{0}{1}_Update.csv", rp.BulkPath, rp.SFTableInfo.TableName);
            if (File.Exists(path))
                File.Delete(path);
            bqs.ExecuteBlockingQuery(rp.SFTableInfo.TableName, ConcurrencyMode.Parallel, query, 30000, path);
            Logger.Log(FunctionType.DownloadData, rp.SFTableInfo.TableName, TableTaskType.Update, start, -1);

            using (TextReader tr = new StreamReader(path))
            {
                Logger.Log(FunctionType.BulkQuery, rp.SFTableInfo.TableName, TableTaskType.Update, start, -1);
                string firstLine = tr.ReadLine();
                if (firstLine != "Records not found for this query")
                {
                    ((StreamReader)tr).BaseStream.Position = 0;
                    ((StreamReader)tr).DiscardBufferedData();

                    using (CsvReader csv = new CsvReader(tr, true))
                    {
                        using (CSVDataReaderAdapter adapter = new CSVDataReaderAdapter(csv))
                        {
                            IDataReader reader = adapter;
                            SqlConnection conn = Dest;
                            if (conn.State != ConnectionState.Open)
                            {
                                conn.Open();
                            }
                            string tablename = rp.SFTableInfo.GetStagingUpdateTableName();
                            Dictionary<string, string> lookup = TableUtilities.GetDBCasedNames(tablename, conn);

                            List<string> decimals = TableUtilities.GetDBDecimals(rp.SFTableInfo.TableName, Dest);
                            foreach (string decimalColumn in decimals)
                            {
                                adapter.AddDecimalOveride(decimalColumn);
                            }


                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                            {

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    string s1 = reader.GetName(i);
                                    bulkCopy.ColumnMappings.Add(s1, lookup[s1.ToUpper()]);
                                }

                                bulkCopy.DestinationTableName = string.Format("[{0}]", tablename);
                                bulkCopy.BatchSize = rp.BatchSize;
                                bulkCopy.BulkCopyTimeout = 0; ;
                                bulkCopy.WriteToServer(reader);
                                Logger.Log(FunctionType.BulkLoad, rp.SFTableInfo.TableName, TableTaskType.Update, start, -1);
                            }
                        }
                    }
                }
            }
            try
            {
                File.Delete(path);
            }
            catch
            {
            }

            
            return -1;

        }

        
    }
}
 